package com.iaiai.cobra.repository.config;

import org.apache.commons.lang3.time.DateFormatUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * Package: com.txt.natural.repository.config.db
 * Author: iaiai
 * Create Time: 2022/3/22 11:44
 * QQ: 176291935
 * Url: https://blog.csdn.net/waterseason
 * Email: 176291935@qq.com
 * Description:
 */
public class DbUpgradeVersion {

    //处理这个版本号需要执行复杂sql处理文件
    public void handleVersion(Connection connection,PreparedStatement preparedStatement, int version) throws SQLException {
        //沒有處理挎版本處理，只是单个版本处理
        switch (version){
            case 10009: //如果是这个版本，需要处理数据库的数据，在所有sql文件执行完之后需要再处理数据
                dbUpgradeVersion_10009(connection,preparedStatement,version);
                break;
        }
    }

    //处理10009版本
    private void dbUpgradeVersion_10009(Connection connection,PreparedStatement preparedStatement,int version) throws SQLException {
        //读取数据，开单收银
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('till','balanceRefund','memberRefund','orderEdit')" +
                " group by role_id,store_id");

        ResultSet rs = preparedStatement.executeQuery();
        List<Map<String,Object>> list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,1l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //开单收银
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1001,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('till')" +
                " ) a");
        preparedStatement.executeUpdate();

        //储值退款
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1004,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('balanceRefund')" +
                " ) a");
        preparedStatement.executeUpdate();

        //改单作废
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1002,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('orderEdit')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客户退货
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1003,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberRefund')" +
                " ) a");
        preparedStatement.executeUpdate();


        //读取数据，员工
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('roleManager','employeeManager','commissionSetting')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,2l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //岗位权限
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1005,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('roleManager')" +
                " ) a");
        preparedStatement.executeUpdate();

        //员工管理
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1006,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('employeeManager')" +
                " ) a");
        preparedStatement.executeUpdate();

        //提成设置
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1007,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('commissionSetting')" +
                " ) a");
        preparedStatement.executeUpdate();


        //读取数据，商品
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsManager','comboManager','goodsType','unit','goodsDiscount')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,3l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //商品管理
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1008,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsManager')" +
                " ) a");
        preparedStatement.executeUpdate();

        //套餐管理
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1009,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('comboManager')" +
                " ) a");
        preparedStatement.executeUpdate();

        //商品分类
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1010,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsType')" +
                " ) a");
        preparedStatement.executeUpdate();

        //规格单位
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1011,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('unit')" +
                " ) a");
        preparedStatement.executeUpdate();

        //商品折扣开/关
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1012,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsDiscount')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，客户
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberManager','memberDistribution','memberVisit','memberVisitSetting','memberTag','memberGrade','memberGradePromotion')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,4l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //客户管理
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1013,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberManager')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客户分配
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1014,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberDistribution')" +
                " ) a");
        preparedStatement.executeUpdate();

        //回访提醒
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1015,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberVisit')" +
                " ) a");
        preparedStatement.executeUpdate();

        //回访设置
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1016,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberVisitSetting')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客户标签
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1017,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberTag')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客户等级
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1018,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberGrade')" +
                " ) a");
        preparedStatement.executeUpdate();

        //等级晋升
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1019,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('memberGradePromotion')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，经营
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('account','revexpType','payMethod','copyStore')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,5l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //记账本
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1020,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('account')" +
                " ) a");
        preparedStatement.executeUpdate();

        //收支分类
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1021,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('revexpType')" +
                " ) a");
        preparedStatement.executeUpdate();

        //支付方式
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1022,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('payMethod')" +
                " ) a");
        preparedStatement.executeUpdate();

        //复用首页
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1023,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('copyStore')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，积分
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsGet','pointsReward','pointsRewardStat','pointsRewardSetting','pointsSub','pointsStat')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,6l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //积分获取
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1024,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsGet')" +
                " ) a");
        preparedStatement.executeUpdate();

        //积分奖励
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1025,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsReward')" +
                " ) a");
        preparedStatement.executeUpdate();

        //奖励统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1026,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsRewardStat')" +
                " ) a");
        preparedStatement.executeUpdate();

        //奖励设置
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1027,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsRewardSetting')" +
                " ) a");
        preparedStatement.executeUpdate();

        //积分清减
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1028,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsSub')" +
                " ) a");
        preparedStatement.executeUpdate();

        //积分统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1029,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('pointsStat')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，库存
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsIn','goodsOut','deposit','inventoryChecking','reportDepositRecords','inventoryQuery','inOutStatistics','inventoryReport','inventorySetting')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,7l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //商品入库
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1030,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsIn')" +
                " ) a");
        preparedStatement.executeUpdate();

        //商品出库
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1031,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('goodsOut')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客存商品
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1032,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('deposit')" +
                " ) a");
        preparedStatement.executeUpdate();

        //库存盘点
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1033,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('inventoryChecking')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客存存取记录
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1034,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportDepositRecords')" +
                " ) a");
        preparedStatement.executeUpdate();

        //库存查询
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1035,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('inventoryQuery')" +
                " ) a");
        preparedStatement.executeUpdate();

        //出入库统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1036,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('inOutStatistics')" +
                " ) a");
        preparedStatement.executeUpdate();

        //盘点报告
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1037,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('inventoryReport')" +
                " ) a");
        preparedStatement.executeUpdate();

        //盘点设置
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1038,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('inventorySetting')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，财报
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportCommission','reportPerformance','reportDepositStat','reportDepositSummary','reportSale','reportSummary','reportCash','refundAccount')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,10l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //业绩统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1047,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportCommission')" +
                " ) a");
        preparedStatement.executeUpdate();

        //绩效统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1048,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportPerformance')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客存统计
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1049,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportDepositStat')" +
                " ) a");
        preparedStatement.executeUpdate();

        //客存汇总
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1050,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportDepositSummary')" +
                " ) a");
        preparedStatement.executeUpdate();

        //销售分析
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1051,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportSale')" +
                " ) a");
        preparedStatement.executeUpdate();

        //收支汇总
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1052,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportSummary')" +
                " ) a");
        preparedStatement.executeUpdate();

        //收银对账
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1053,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('reportCash')" +
                " ) a");
        preparedStatement.executeUpdate();

        //退货对账
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1054,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('refundAccount')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，套餐营销
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from bm_role_plate a" +
                " left join bm_plate b on a.plate_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingComboActivitySetting','marketingComboActivityAnalysis')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,8l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //活动设置
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1040,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingComboActivitySetting')" +
                " ) a");
        preparedStatement.executeUpdate();

        //数据分析
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1041,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingComboActivityAnalysis')" +
                " ) a");
        preparedStatement.executeUpdate();

        //读取数据，积分营销
        preparedStatement = connection.prepareStatement("select a.role_id,b.name,b.code,c.store_id from bm_role_plate a" +
                " left join bm_plate b on a.plate_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsDeduction','marketingPointsMemberMultiple','marketingPointsActivityMultiple','marketingPointsMall','marketingPointsSummary')" +
                " group by role_id,store_id");

        rs = preparedStatement.executeQuery();
        list = new ArrayList<>();
        while(rs.next()){
            Long roleId = rs.getLong("role_id");
            String pageItemName = rs.getString("name");
            String pageItemCode = rs.getString("code");
            Long storeId = rs.getLong("store_id");

            Map<String,Object> map = new HashMap<>();
            map.put("roleId",roleId);
            map.put("name",pageItemName);
            map.put("code",pageItemCode);
            map.put("storeId",storeId);
            list.add(map);
        }
        rs.close();

        for (Map<String, Object> map : list) {
            Long storeId = (Long) map.get("storeId");
            Long roleId = (Long) map.get("roleId");

            preparedStatement = connection.prepareStatement("INSERT INTO b_role_page_items(`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`) VALUES(?,?,?,?,?,?)");
            preparedStatement.setLong(1,storeId);
            preparedStatement.setLong(2,roleId);
            preparedStatement.setLong(3,9l);
            preparedStatement.setInt(4,0);
            preparedStatement.setString(5, DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.setString(6,DateFormatUtils.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            preparedStatement.executeUpdate();
        }

        //积分抵扣
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1042,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsDeduction')" +
                " ) a");
        preparedStatement.executeUpdate();

        //会员多倍
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1043,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsMemberMultiple')" +
                " ) a");
        preparedStatement.executeUpdate();

        //活动多倍
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1044,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsActivityMultiple')" +
                " ) a");
        preparedStatement.executeUpdate();

        //积分商城
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1045,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsMall')" +
                " ) a");
        preparedStatement.executeUpdate();

        //积分使用汇总
        preparedStatement = connection.prepareStatement("insert into b_role_page_items (`store_id`,`role_id`,`page_items_id`,`del`,`create_time`,`modify_time`)" +
                " select a.* from(" +
                " select c.store_id,a.role_id,1046,0,a.create_time,a.modify_time from b_role_function_item a" +
                " left join b_function_item b on a.function_item_id = b.id" +
                " left join b_role c on a.role_id=c.id" +
                " where a.del=0 and b.`code` in ('marketingPointsSummary')" +
                " ) a");
        preparedStatement.executeUpdate();

    }

}
